#!/usr/bin/env python
#coding=utf8
"""
@描述：更新jobs表中的industryCode字段
@日期：2015年01月25日 星期日 家
"""
import sys
import MySQLdb
import re

# 从数据库中取出每一行的id+industry，根据industry判断是否在industry_tag中，
# 若在，则更新industryCode字段（只更新一次）
def openConn(host,uname,passwd):
	conn = MySQLdb.connect(host=host,user=uname,passwd=passwd,db="jobs",port=3306,charset="utf8")
	return conn
if __name__ == "__main__":
	conn = openConn("192.168.1.251","root","")
	update_conn = openConn("192.168.1.251","root","")
	update_cur = update_conn.cursor() # update cursor
	# 原来在数据量持续到100w以后，则非常慢
	# select = "select id,industry from jobs order by id desc limit 0,5000"
	# 修改后，用id筛选，则速度提升很多
	select = "select id,industry from jobs where id<%s order by id desc limit 0,5000"
	cur_select = conn.cursor()
	index = 0
	map_industry = {}
	industry_tag = "SELECT firstName,firstId,secondName,secondId FROM industry_tag"
	industry_cur = conn.cursor()
	industry_cur.execute(industry_tag)
	map_result = industry_cur.fetchall()
	records = 0
	maxId = sys.maxint
	for (firstName,firstId,secondName,secondId) in map_result:
		map_industry[firstName] = firstId
		map_industry[secondName] = secondId
	# while与for在此的区别
	while(True):
		cur_select.execute(select,[maxId])
		print(select %(maxId))
		result_select = cur_select.fetchall()
		limit = 0
		for (id,industry) in result_select:
			maxId = min(id,maxId)
			index += 1
			limit += 1
			industryCode = None
			if(industry is "[]" or industry is None):
				continue
			# 返回值
			industry = industry.strip().replace("\"","").replace("[","").replace("]","").replace("\\",",")
			industrys = re.split(",|/",industry) # split可以用正则表达式
			for industry_1 in industrys:
				industry_1 = industry_1.replace(")","").replace("(","").replace(u"（","").replace(u"）","")
				if(industry_1 not in map_industry):
					continue
				industryCode = map_industry.get(industry_1)
				update = "update jobs set industry_code = %s where id=%s"
				update_cur.execute(update,(industryCode,id))
				break;
		records += 5000
		print("update %d records!" %(records))
		update_conn.commit()
		if(limit < 100):
			break
